<?php

// $Id$

require_once(TANGRA_MAIN_DIR.'interfaces/i_db_storable.class.php');
require_once(TANGRA_MAIN_DIR.'nls/date.inc.php');


require_once('tcms_vpage.class.php');


class TCMS_VPage_DBC extends TCMS_VPage implements I_DB_Storable {
	public function save(DB_Connection $dbc) {
		$ret = false;

		if ($this->get_id()) {
			$ret = $this->update($dbc);
		} else {
			$ret = $this->insert($dbc);
			$this->set_id($ret);
		}

		return $ret;
	}


	public function load_by_id(DB_Connection $dbc, $id) {
		tangra_if_not_int_throw_e($id);

		$ret = false;

		$sql = "select path, template, online, created, last_modified, ".
						"cache, cache_expiry, cache_locked, created_by, last_modified_by, ".
						"sys_generated, in_sitemap, sitemap_weight, sitemap_change_freq ".
					" from tcms_vpages where id = $id";
		$rez = $dbc->execute($sql);

		if (!$rez->is_eof()) {
			$rez_obj = $rez->fetch_object();

			$this->set_id($id);
			$this->set_path(stripslashes($rez_obj->PATH));
			$this->set_template($rez_obj->TEMPLATE);
			$this->set_online($rez_obj->ONLINE);
			$this->set_created($rez_obj->CREATED);
			$this->set_last_modified($rez_obj->LAST_MODIFIED);
			$this->set_cache($rez_obj->CACHE);
			$this->set_cache_expiry($rez_obj->CACHE_EXPIRY);
			$this->set_cache_locked($rez_obj->CACHE_LOCKED);
			$this->set_created_by($rez_obj->CREATED_BY);
			$this->set_last_modified_by($rez_obj->LAST_MODIFIED_BY);
			$this->set_sys_generated($rez_obj->SYS_GENERATED);
			$this->set_in_sitemap($rez_obj->IN_SITEMAP);
			$this->set_sitemap_weight($rez_obj->SITEMAP_WEIGHT);
			$this->set_sitemap_change_freq($rez_obj->SITEMAP_CHANGE_FREQ);
			$ret = $id;
		}

		return $ret;
	}


	protected function insert(DB_Connection $dbc) {
		$ret = false;

		$id = $dbc->generate_id('tcms_vpages_seq');
		$path = addslashes($this->get_path());
		$template = $this->get_template();
		$online = $this->get_online() ? 1 : 0;
		$created = date('Y-m-d H:i:s');
		$last_modified = $created;
		$cache = $this->get_cache() ? 1 : 0;
		$cache_expiry = $this->get_cache_expiry();
		$cache_locked = $this->get_cache_locked() ? 1 : 0;
		$created_by = $this->get_created_by();
		$last_modified_by = $created_by;
		$sys_generated = $this->get_sys_generated() ? 1 : 0;
		$in_sitemap = $this->get_in_sitemap() ? 1 : 0;
		$sitemap_weight = $this->get_sitemap_weight();
		$sitemap_change_freq = $this->get_sitemap_change_freq();

		if (!$this->is_unique_path_insert($dbc, $path)) {
			throw new Tangra_Module_Exception('Not unique path: '.$path);
		}

		if ($id) {
			$sql = "insert into tcms_vpages (id, ".
									      "path, ".
									      "template, ".
									      "online, ".
									      "created, ".
									      "last_modified, ".
									      "cache, ".
									      "cache_expiry, ".
									      "cache_locked, ".
									      "created_by, ".
									      "last_modified_by, ".
									      "sys_generated, ".
									      "in_sitemap, ".
									      "sitemap_weight, ".
									      "sitemap_change_freq ".
									      ") ".
								"values ".
									      "($id,".
									      "'$path', ".
									      "$template, ".
									      "$online, ".
									      "'$created', ".
									      "'$last_modified', ".
									      "$cache, ".
									      "$cache_expiry, ".
									      "$cache_locked, ".
									      "$created_by, ".
									      "$last_modified_by, ".
									      "$sys_generated, ".
									      "$in_sitemap, ".
									      "$sitemap_weight, ".
									      "$sitemap_change_freq ".
									      ")";
			$dbc->execute($sql);
			$ret = $id;
		} else {
			throw new TE_Exception('tcms_vpages_seq');
		}

		return $ret;
	}


	protected function update(DB_Connection $dbc) {
		$id = $this->get_id();

		$path = addslashes($this->get_path());
		$template = $this->get_template();
		$online = $this->get_online() ? 1 : 0;
		$created = $this->get_created();
		$last_modified = date('Y-m-d H:i:s');
		$cache = $this->get_cache() ? 1 : 0;
		$cache_expiry = $this->get_cache_expiry();
		$cache_locked = $this->get_cache_locked() ? 1 : 0;
		$created_by = $this->get_created_by();
		$last_modified_by = $this->get_last_modified_by();
		$sys_generated = $this->get_sys_generated() ? 1 : 0;
		$in_sitemap = $this->get_in_sitemap() ? 1 : 0;
		$sitemap_weight = $this->get_sitemap_weight();
		$sitemap_change_freq = $this->get_sitemap_change_freq();

		if (!$this->is_unique_path_update($dbc, $path, $id)) {
			throw new Tangra_Module_Exception('Not unique path: '.$path);
		}


		$sql = "update tcms_vpages set ".
						"path = '$path', ".
						"template = $template, ".
						"online = $online, ".
						"created = '$created', ".
						"last_modified = '$last_modified', ".
						"cache = $cache, ".
						"cache_expiry = $cache_expiry, ".
						"cache_locked = $cache_locked, ".
						"created_by = $created_by, ".
						"last_modified_by = $last_modified_by, ".
						"sys_generated = $sys_generated, ".
						"in_sitemap = $in_sitemap, ".
						"sitemap_weight = $sitemap_weight, ".
						"sitemap_change_freq = $sitemap_change_freq ".
					"where id = ".$id;

		$dbc->execute($sql);
		$ret = $id;

		return $ret;
	}


	protected function is_unique_path_insert(DB_Connection $dbc, $path) {
		$sql = "select id from tcms_vpages where path = '$path'";
		$rez = $dbc->execute($sql);

		return $rez->is_eod();
	}


	protected function is_unique_path_update(DB_Connection $dbc, $path, $id) {
		$sql = "select id from tcms_vpages where path = '$path' and id <> $id";

		$rez = $dbc->execute($sql);

		return $rez->is_eod();
	}


	public static function get_sql_for_grid($path, $online, $template, $sys_gen, $cache = false) {
		$where = TCMS_VPage_DBC::prepare_where_clause($path, $online, $template, $sys_gen, $cache);
		if ($where) {
			$where = " and ".$where;
		}

		$sql = "select p.id, ".
						"p.path, ".
						"t.name, ".
						"p.online, ".
						"p.sys_generated, ".
						"p.in_sitemap, ".
						"p.sitemap_weight, ".
						"t.id ".
					"from tcms_vpages p, ".
							"tcms_vpage_templates t ".
					"where p.template = t.id".$where." order by p.path";

		return $sql;
	}


	public static function get_sql_count_for_grid($path, $online, $template, $sys_gen, $cache = false) {
		$where = TCMS_VPage_DBC::prepare_where_clause($path, $online, $template, $sys_gen, $cache);
		if ($where) {
			$where = " where ".$where;
		}
		$sql = "select count(id) as total_rows from tcms_vpages p ".$where;

		return $sql;
	}


	public function load_by_path(DB_Connection $dbc, $path) {
		$ret = false;

		$path = addslashes($path);

		$sql = "select id from tcms_vpages where path = '$path'";

		$rez = $dbc->execute($sql);
		if (!$rez->is_eod()) {
			$rez_obj = $rez->fetch_object();
			$ret = $this->load_by_id($dbc, $rez_obj->ID);
		}


		return $ret;
	}


	public static function sql_get_pages_for_sitemap() {
		$sql = "select path, sitemap_weight, sitemap_change_freq, last_modified from tcms_vpages where online = 1 and in_sitemap = 1";

		return $sql;
	}


	public static function is_unique_path(DB_Connection $dbc, $path, $id) {
		tangra_if_not_int_throw_e($id);
		$path = addslashes($path);

		$sql = "select id from tcms_vpages where id <> $id and path = '$path' ";
		$rez = $dbc->execute($sql);

		return $rez->is_eod();
	}


	public static function update_last_modified(DB_Connection $dbc, $vpage_id, $user_id = false, $last_modified = false) {
		tangra_if_not_int_throw_e($vpage_id);

		if ($last_modified) {
			if (!check_timestamp($last_modified)) {
				throw new Tangra_Module_Exception('Invalid datetime: '.$last_modified);
			}
		} else {
			$last_modified = date('Y-m-d H:i:s');
		}

		if ($user_id) {
			tangra_if_not_int_throw_e($user_id);
			$sql = "update tcms_vpages set last_modified = '$last_modified', last_modified_by = $user_id where id = $vpage_id";
		} else {
			$sql = "update tcms_vpages set last_modified = '$last_modified' where id = $vpage_id";
		}

		$rez = $dbc->execute($sql);
	}


	private static function remove_duplicate_asteriks($filter) {
		$ret = $filter;
		while(strpos($ret, '**') !== false) {
			$ret = str_replace('**', '*', $ret);
		}

		return $ret;
	}


	private static function prepare_where_clause($path = '', $online = false, $template = false, $sys_gen = false, $cache = false) {
		$path = TCMS_VPage_DBC::remove_duplicate_asteriks($path);

		if ($path || $online || $template || $sys_gen || $cache) {
			$where = "";
			if ($path) {
				if (strpos($path, '*') !== false) {
					$path = addslashes($path);
					$path = str_replace('*', '%', $path);
					$where .= "p.path like '$path' ";
				} else {
					$path = addslashes($path);
					$where .= "p.path = '$path' ";
				}
			}

			switch($online) {
				case 1:
					$where .= ($where ? ' and ' : '')." online = 1";
					break;
				case 2:
					$where .= ($where ? ' and ' : '')." online = 0";
					break;
			}

			if ($template) {
				$where .= ($where ? ' and ' : '')." template = ".$template;
			}


			switch($sys_gen) {
				case 1:
					$where .= ($where ? ' and ' : '')." sys_generated = 1";
					break;
				case 2:
					$where .= ($where ? ' and ' : '')." sys_generated = 0";
					break;
			}


			switch($cache) {
				case 1:
					$where .= ($where ? ' and ' : '')." cache_locked = 1";
					break;
				case 2:
					$where .= ($where ? ' and ' : '')." cache_locked = 0";
					break;
			}
		} else {
			$where = '';
		}

		return $where;
	}


	public static function delete(DB_Connection $dbc, $vpage_id) {
		tangra_if_not_int_throw_e($vpage_id);

		$sql = "delete from tcms_vpages where id = $vpage_id";
		$dbc->execute($sql);
	}


	public static function set_status_online(DB_Connection $dbc, $vpage_id, $online, $user_id) {
		tangra_if_not_int_throw_e($vpage_id);

		$online = $online ? 1 : 0;

		$sql = "update tcms_vpages set online = $online where id = $vpage_id";
		$dbc->execute($sql);

		TCMS_VPage_DBC::update_last_modified($dbc, $vpage_id, $user_id);
	}


	public static function count_pages_with_template(DB_Connection $dbc, $template_id, $just_online = false) {
		tangra_if_not_int_throw_e($template_id);

		$ret = 0;

		if ($just_online) {
			$sql = "select count(id) as c from tcms_vpages where template = $template_id and online = 1";
		} else {
			$sql = "select count(id) as c from tcms_vpages where template = $template_id";
		}
		$rez = $dbc->execute($sql);
		if (!$rez->is_eod()) {
			$rez_obj = $rez->fetch_object();
			$ret = $rez_obj->C;
		}

		return $ret;
	}


	public static function load_all_vpage_ids_for_template(DB_Connection $dbc, $template_id) {
		tangra_if_not_int_throw_e($template_id);

		$ret = array();

		$sql = "select id from tcms_vpages where template = $template_id";
		$rez = $dbc->execute($sql);
		while ($rez_obj = $rez->fetch_object()) {
			$ret[] = $rez_obj->ID;
		}

		return $ret;
	}


	public static function turn_sys_generated(DB_Connection $dbc, $vpage_id, $sys_gen) {
		tangra_if_not_int_throw_e($vpage_id);

		$sys_gen = $sys_gen ? 1 : 0;
		$sql = "update tcms_vpages set sys_generated = $sys_gen where id = $vpage_id";
		$rez = $dbc->execute($sql);
	}


	public static function update_last_modified_for_sa(DB_Connection $dbc, $user_id, $t_sa_id) {
		tangra_if_not_int_throw_e($user_id);
		tangra_if_not_int_throw_e($t_sa_id);
		$last_modified = date('Y-m-d H:i:s');

		$sql = "update tcms_vpages, tcms_vpage_system_attributes set tcms_vpages.last_modified = '$last_modified', tcms_vpages.last_modified_by = $user_id where tcms_vpage_system_attributes.t_sa_id = $t_sa_id and tcms_vpage_system_attributes.vpage = tcms_vpages.id";
		$dbc->execute($sql);
	}


	public static function update_last_modified_for_ci(DB_Connection $dbc, $user_id, $ci_id) {
		tangra_if_not_int_throw_e($user_id);
		tangra_if_not_int_throw_e($ci_id);
		$last_modified = date('Y-m-d H:i:s');

		$sql = "update tcms_vpages, tcms_vpage_cis set tcms_vpages.last_modified = '$last_modified', tcms_vpages.last_modified_by = $user_id where tcms_vpage_cis.ci_id = $ci_id and tcms_vpage_cis.vpage = tcms_vpages.id";

		$dbc->execute($sql);
	}


	public static function update_cache_settings_for_template(DB_Connection $dbc, $template_id, $cache, $cache_expiry) {
		tangra_if_not_int_throw_e($template_id);
		tangra_if_not_int_throw_e($cache_expiry);

		$cache = $cache ? 1 : 0;
		$sql = "update tcms_vpages set cache = $cache, cache_expiry = $cache_expiry where template = $template_id";
		$dbc->execute($sql);
	}
	
	
	public static function vpage_with_path_exists(DB_Connection $dbc, $path) {
		$path = addslashes($path);
	
		$sql = "select id from tcms_vpages where path = '$path'";
		$rez = $dbc->execute($sql);
	
		return !$rez->is_eod();
	}
	
}